package domain.web.dao.impl;

import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import domain.web.dao.DetailDao;
import domain.web.model.Detail;
import domain.web.model.DetailView;

@Repository("detailDao")
public class DetailDaoImpl extends JdbcDaoSupport implements DetailDao {

	@Autowired
	private DataSource dataSource;

	private NumberFormat fm;

	@PostConstruct
	void init() {
		setDataSource(dataSource);
		fm = NumberFormat.getNumberInstance();
	}

	private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");

	@Override
	public void insert(Detail detail) {
		String sql = "INSERT INTO Details(ProductId, StoreId, Price, ProductUrl, ImageUrl, DDate) "
				+ "VALUES(?,?,?,?,?, NOW())";
		getJdbcTemplate().update(sql, detail.getProductId(),
				detail.getStoreId(), detail.getPrice(), detail.getProductUrl(),
				detail.getImageUrl());
	}

	@Override
	public void update(Detail detail) {
		String sql = "UPDATE Details "
				+ "SET Price = ?, ProductUrl = ?, ImageUrl = ?, DDate = NOW() "
				+ "WHERE ProductId = ? AND StoreId = ? ";
		getJdbcTemplate().update(sql, detail.getPrice(),
				detail.getProductUrl(), detail.getImageUrl(),
				detail.getProductId(), detail.getStoreId());
	}

	@Override
	public void delete(long productId, long storeId) {
		String sql = "DELETE FROM Details WHERE Productid = ? AND StoreId = ?";
		getJdbcTemplate().update(sql, productId, storeId);
	}

	@Override
	public Detail get(long productId, long storeId) {
		String sql = "SELECT * FROM Details WHERE ProductId = ? AND StoreId = ? ";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				productId, storeId);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public Detail get(String uri, long storeId) {
		String sql = "SELECT d.* FROM Details d RIGHT JOIN Products p ON d.ProductId = p.Id "
				+ "WHERE Uri = ? AND StoreId = ? ";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				uri, storeId);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public List<DetailView> getViewListByProduct(long productId) {
		String sql = "SELECT * FROM DetailView WHERE ProductId = ? ";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				productId);
		List<DetailView> views = new ArrayList<DetailView>();
		for (Map<String, Object> map : results) {
			views.add(rowMapperView(map));
		}
		return views;
	}

	private Detail rowMapper(Map<String, Object> row) {
		Detail detail = new Detail();
		detail.setProductId((Long) row.get("ProductId"));
		detail.setStoreId((Long) row.get("StoreId"));
		detail.setPrice((Long) row.get("Price"));
		detail.setProductUrl((String) row.get("ProductUrl"));
		detail.setImageUrl((String) row.get("ImageUrl"));
		Date date = (Date) row.get("DDate");
		detail.setDate(dateFormat.format(date));
		return detail;
	}

	private DetailView rowMapperView(Map<String, Object> row) {
		DetailView view = new DetailView();
		view.setProductId((Long) row.get("ProductId"));
		view.setProductName((String) row.get("ProductName"));
		view.setStoreId((Long) row.get("StoreId"));
		view.setStoreName((String) row.get("StoreName"));
		view.setStoreWebsite((String) row.get("Website"));
		view.setStoreImageUrl((String) row.get("StoreImageUrl"));

		Long price = (Long) row.get("Price");
		view.setPrice(fm.format(price) + " VND");
		view.setProductUrl((String) row.get("ProductUrl"));
		view.setImageUrl((String) row.get("ImageUrl"));

		Date date = (Date) row.get("DDate");
		view.setDate(dateFormat.format(date));

		return view;
	}

	public void updateImage(long storeId) {
		String sql = "SELECT * FROM Details WHERE StoreId = ? AND Imageurl = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				storeId, "http://www.dienmaycholon.vn/img/box-red.ico");
		for (Map<String, Object> map : results) {
			long pid = (Long) map.get("ProductId");
			String upSql = "SELECT ImageUrl FROM Details WHERE StoreId = 1 AND ProductId = ?";
			List<Map<String, Object>> upResults = getJdbcTemplate().queryForList(upSql, pid);
			String newImage = "http://localhost:8080/psw/img/default.jpg";
			if(!upResults.isEmpty()) {
				newImage = (String) upResults.get(0).get("ImageUrl");
			}
			
			upSql = "UPDATE Details SET ImageUrl = ? WHERE ProductId = ? AND StoreID = ?";
			getJdbcTemplate().update(upSql, newImage, pid, storeId);
		}

	}
}
